using System.Data;
using System.Windows;
using MySql.Data.MySqlClient;
using WpfApp1.Models;

namespace WpfApp1.DataAccess;

public class CourseSelectDataAccess : ICourseSelectDataAccess
{
    //选择课程
    public void SelectCourses(string courseNumber, string studentNumber)
    {
        using (var connection = SqlAccess.CreateConnection())
        {
            connection.Open();
            try
            {
                // 检查是否已经选过这门课
                var checkSql = "SELECT COUNT(*) FROM course_selection WHERE student_number = @studentNumber AND course_number = @courseNumber";
                var checkCmd = new MySqlCommand(checkSql, connection);
                checkCmd.Parameters.AddWithValue("@studentNumber", studentNumber);
                checkCmd.Parameters.AddWithValue("@courseNumber", courseNumber);
                var count = Convert.ToInt32(checkCmd.ExecuteScalar());

                if (count > 0)
                {
                    MessageBox.Show("您已经选过这门课程了！");
                    return;
                }

                // 插入选课记录
                var sql = @"INSERT INTO course_selection 
                        (student_number, course_number, grades) 
                        VALUES (@studentNumber, @courseNumber, 0)";
                
                var command = new MySqlCommand(sql, connection);
                command.Parameters.AddWithValue("@studentNumber", studentNumber);
                command.Parameters.AddWithValue("@courseNumber", courseNumber);
                
                var row = command.ExecuteNonQuery();
                MessageBox.Show(row == 1 ? "选课成功！" : "选课失败！");
            }
            catch (MySqlException ex)
            {
                MessageBox.Show($"选课失败：{ex.Message}");
            }
        }
    }

    /**
     * 查询所有学生选课信息(管理员)
     */
    public List<StudentCourseSelection> GetStudentCourses()
    {
        var studentCourseDtoList = new List<StudentCourseSelection>();
        using (var connection = SqlAccess.CreateConnection())
        {
            connection.Open();
            var sql = $"select c.course_number,c.course_name,c.credits,c.hours,t.name teacher_name ,cs.grades,s.student_number,s.name student_name "+
                      " from course_selection cs,courses c , students s,teachers t "+
                      " where cs.course_number  = c.course_number and cs.student_number = s.student_number and c.teacher_number = t.teacher_number ;                                                            ";
            
            var command = new MySqlCommand(sql, connection);
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    studentCourseDtoList.Add(new StudentCourseSelection(
                        reader.GetString("course_number"), 
                        reader.GetString("course_name"),

                        reader.GetString("student_number"), 
                        reader.GetString("student_name"),
                        
                        reader.GetInt32("credits"),
                        reader.GetInt32("hours"),
                        reader.GetString("teacher_name"),
                        reader.GetInt32("grades")
                    ));
                    
                }
            }
        }
        return studentCourseDtoList;
    }
    /**
     * 根据学生Number查询选课信息(管理员和学生查看选课信息)
     */
    public List<StudentCourseSelection> GetStudentCoursesByStudentNumber(CourseSelection courseSelection)
    {
        var studentCourseSelectionList = new List<StudentCourseSelection>();
        using (var connection = SqlAccess.CreateConnection())
        {
            
            connection.Open();
            var sql = "select c.course_number,c.course_name,c.credits,c.hours,t.name teacher_name,cs.grades,s.student_number,s.name student_name "+
                      " from course_selection cs,courses c , students s,teachers t "+
                      $"where cs.course_number  = c.course_number and cs.student_number = s.student_number and c.teacher_number = t.teacher_number and s.student_number = '{courseSelection.StudentNumber}';                                                            ";
            
            var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@courseNumber", courseSelection.CourseNumber);
            
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    studentCourseSelectionList.Add(new StudentCourseSelection(
                        reader.GetString("course_number"),  // 转换为字符串
                        reader.GetString("course_name"),

                        reader.GetString("student_number"), // 转换为字符串
                        reader.GetString("student_name"),
                        
                        reader.GetInt32("credits"),
                        reader.GetInt32("hours"),
                        reader.GetString("teacher_name"),
                        reader.GetInt32("grades")
                    ));
                }
            }
        }

        if (studentCourseSelectionList.Count == 0 && !string.IsNullOrEmpty(courseSelection.CourseNumber))
        {
            MessageBox.Show("未找到相关选课信息");
        }
        return studentCourseSelectionList;
    }

    /**
     * 根据课程Number查询选课信息
     */
    public List<StudentCourseSelection> GetStudentCoursesByCourseNumber(CourseSelection courseSelection)
    {
        var studentCourseSelectionList = new List<StudentCourseSelection>();
        using (var connection = SqlAccess.CreateConnection())
        {
            
            connection.Open();
            var sql = "select c.course_number,c.course_name,c.credits,c.hours,t.name teacher_name,cs.grades,s.student_number,s.name student_name "+
                      " from course_selection cs,courses c , students s,teachers t "+
                      $"where cs.course_number  = c.course_number and cs.student_number = s.student_number and c.teacher_number = t.teacher_number and c.course_number = '{courseSelection.CourseNumber}';                                                            ";
            
            var command = new MySqlCommand(sql, connection);
            
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    studentCourseSelectionList.Add(new StudentCourseSelection(
                        reader.GetString("course_number"),  // 转换为字符串
                        reader.GetString("course_name"),

                        reader.GetString("student_number"), // 转换为字符串
                        reader.GetString("student_name"),
                        
                        reader.GetInt32("credits"),
                        reader.GetInt32("hours"),
                        reader.GetString("teacher_name"),
                        reader.GetInt32("grades")
                    ));
                }
            }
        }

        if (studentCourseSelectionList.Count == 0)
        {
            MessageBox.Show("该课程号不存在");
        }
        return studentCourseSelectionList;
    }

    public List<StudentCourseSelection> AdminGetStudentSelectCoursesByNumber(CourseSelection courseSelection)
    {
        var StudentCourseSelectionList = new List<StudentCourseSelection>();
        using (var connection = SqlAccess.CreateConnection())
        {
            
            connection.Open();
            var sql = "select c.course_number,c.course_name,c.credits,c.hours,t.name teacher_name,cs.grades,s.student_number,s.name student_name "+
                      " from course_selection cs,courses c , students s,teachers t "+
                      $"where cs.course_number  = c.course_number and cs.student_number = s.student_number and c.teacher_number = t.teacher_number and s.student_number = '{courseSelection.StudentNumber}';                                                            ";
            
            var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@courseNumber", courseSelection.CourseNumber);
            
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    StudentCourseSelectionList.Add(new StudentCourseSelection(
                        reader.GetString("course_number"),  // 转换为字符串
                        reader.GetString("course_name"),

                        reader.GetString("student_number"), // 转换为字符串
                        reader.GetString("student_name"),
                        
                        reader.GetInt32("credits"),
                        reader.GetInt32("hours"),
                        reader.GetString("teacher_name"),
                        reader.GetInt32("grades")
                    ));
                }
            }
        }

        return StudentCourseSelectionList;
    }
}